package controller

import (
	"database/sql"
	"dsc-server/domain/dto"
	"dsc-server/pkg/model"
	"dsc-server/pkg/service"
	"encoding/json"
	"log"
	"net/http"
	"strconv"

	"github.com/gin-gonic/gin"
	"gorm.io/gorm"
)

// TableController 表结构控制器
type TableController struct {
	BaseController
	DB                *gorm.DB
	DataSourceService *service.DataSourceService
}

// NewTableController 创建TableController实例
func NewTableController(db *gorm.DB) *TableController {
	return &TableController{
		DB:                db,
		DataSourceService: service.NewDataSourceService(db),
	}
}

// GetAllTables 获取所有表
func (ctrl *TableController) GetAllTables(c *gin.Context) {
	sourceIdStr := c.Query("sourceId")
	sourceId, err := strconv.ParseUint(sourceIdStr, 10, 32)
	if err != nil {
		c.JSON(http.StatusOK, dto.FailureMessage(nil, "数据源ID参数错误"))
		return
	}

	// 获取数据源信息
	dataSource, err := ctrl.DataSourceService.Detail(uint(sourceId))
	if err != nil {
		c.JSON(http.StatusOK, dto.FailureMessage(nil, "获取数据源信息失败"))
		return
	}

	// 获取数据库连接
	db, err := ctrl.getConnection(dataSource)
	if err != nil {
		c.JSON(http.StatusOK, dto.FailureMessage(nil, "获取数据库连接失败: "+err.Error()))
		return
	}
	defer db.Close()

	// 获取所有表
	tables, err := ctrl.getAllTables(db, dataSource.Type)
	if err != nil {
		c.JSON(http.StatusOK, dto.FailureMessage(nil, "获取表信息失败: "+err.Error()))
		return
	}

	c.JSON(http.StatusOK, tables)
}

// GetAllColumns 获取表的所有列
func (ctrl *TableController) GetAllColumns(c *gin.Context) {
	sourceIdStr := c.Query("sourceId")
	table := c.Query("table")

	sourceId, err := strconv.ParseUint(sourceIdStr, 10, 32)
	if err != nil {
		c.JSON(http.StatusOK, dto.FailureMessage(nil, "数据源ID参数错误"))
		return
	}

	if table == "" {
		c.JSON(http.StatusOK, dto.FailureMessage(nil, "表名不能为空"))
		return
	}

	// 获取数据源信息
	dataSource, err := ctrl.DataSourceService.Detail(uint(sourceId))
	if err != nil {
		c.JSON(http.StatusOK, dto.FailureMessage(nil, "获取数据源信息失败"))
		return
	}

	// 获取数据库连接
	db, err := ctrl.getConnection(dataSource)
	if err != nil {
		c.JSON(http.StatusOK, dto.FailureMessage(nil, "获取数据库连接失败: "+err.Error()))
		return
	}
	defer db.Close()

	// 获取表的所有列
	columns, err := ctrl.getTableColumns(db, dataSource.Type, table)
	if err != nil {
		c.JSON(http.StatusOK, dto.FailureMessage(nil, "获取列信息失败: "+err.Error()))
		return
	}

	c.JSON(http.StatusOK, columns)
}

// getConnection 获取数据库连接
func (ctrl *TableController) getConnection(dataSource *model.Datasource) (*sql.DB, error) {
	var db *sql.DB
	var err error

	switch dataSource.Type {
	case "mysql":
		// 假设URL格式为: host:port/dbname
		db, err = sql.Open("mysql", dataSource.Username+":"+dataSource.Password+"@tcp("+dataSource.Url+")/")
	case "postgres":
		// 假设URL已经是完整的连接字符串
		db, err = sql.Open("postgres", dataSource.Url)
	default:
		return nil, err
	}

	return db, err
}

// getAllTables 获取所有表
func (ctrl *TableController) getAllTables(db *sql.DB, dbType string) ([]string, error) {
	var tables []string
	var rows *sql.Rows
	var err error

	switch dbType {
	case "mysql":
		rows, err = db.Query("SHOW TABLES")
	case "postgres":
		rows, err = db.Query(`
			SELECT table_name 
			FROM information_schema.tables 
			WHERE table_schema = 'public' 
			ORDER BY table_name
		`)
	default:
		return nil, err
	}

	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		var tableName string
		if err := rows.Scan(&tableName); err != nil {
			log.Printf("扫描表名失败: %v", err)
			continue
		}
		tables = append(tables, tableName)
	}

	return tables, nil
}

// getTableColumns 获取表的所有列
func (ctrl *TableController) getTableColumns(db *sql.DB, dbType string, tableName string) ([]map[string]interface{}, error) {
	var columns []map[string]interface{}
	var rows *sql.Rows
	var err error

	switch dbType {
	case "mysql":
		rows, err = db.Query("SHOW FULL COLUMNS FROM " + tableName)
		if err != nil {
			return nil, err
		}
		defer rows.Close()

		for rows.Next() {
			var field, typ, collation, null, key, defaultValue, extra, privileges, comment sql.NullString
			if err := rows.Scan(&field, &typ, &collation, &null, &key, &defaultValue, &extra, &privileges, &comment); err != nil {
				log.Printf("扫描列信息失败: %v", err)
				continue
			}

			column := map[string]interface{}{
				"name":    field.String,
				"type":    typ.String,
				"comment": comment.String,
				"key":     key.String == "PRI",
				"null":    null.String == "YES",
			}

			columns = append(columns, column)
		}
	case "postgres":
		query := `
			SELECT 
				column_name, 
				data_type, 
				is_nullable, 
				column_default,
				(SELECT pg_catalog.col_description(c.oid, cols.ordinal_position::int)
				FROM pg_catalog.pg_class c
				WHERE c.oid = (SELECT ('"' || cols.table_name || '"')::regclass::oid)
				AND c.relname = cols.table_name) as column_comment,
				(SELECT count(*) FROM pg_catalog.pg_constraint con
				INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
				INNER JOIN pg_catalog.pg_attribute att ON att.attrelid = rel.oid AND att.attnum = con.conkey[1]
				WHERE con.contype = 'p' AND rel.relname = cols.table_name AND att.attname = cols.column_name) > 0 as is_primary_key
			FROM information_schema.columns cols
			WHERE table_name = $1
			ORDER BY ordinal_position
		`
		rows, err = db.Query(query, tableName)
		if err != nil {
			return nil, err
		}
		defer rows.Close()

		for rows.Next() {
			var columnName, dataType, isNullable, columnDefault, columnComment sql.NullString
			var isPrimaryKey bool
			if err := rows.Scan(&columnName, &dataType, &isNullable, &columnDefault, &columnComment, &isPrimaryKey); err != nil {
				log.Printf("扫描列信息失败: %v", err)
				continue
			}

			column := map[string]interface{}{
				"name":    columnName.String,
				"type":    dataType.String,
				"comment": columnComment.String,
				"key":     isPrimaryKey,
				"null":    isNullable.String == "YES",
			}

			columns = append(columns, column)
		}
	}

	// 转换为JSON格式
	jsonData, err := json.Marshal(columns)
	if err != nil {
		return nil, err
	}

	var result []map[string]interface{}
	if err := json.Unmarshal(jsonData, &result); err != nil {
		return nil, err
	}

	return result, nil
}
